#!/usr/bin/env python
# -*- coding:utf-8 -*-

# file:do_sqlalchemy1.py
# author:Jackiex
# datetime:2023/5/18 14:10
# software: PyCharm

"""
this is function  description 
"""

from sqlalchemy import Column, String, create_engine
from sqlalchemy.orm import sessionmaker, declarative_base

Base = declarative_base()


class User(Base):
    # 表的名字:
    __tablename__ = 'user'

    # 表的结构:
    id = Column(String(20), primary_key=True)
    name = Column(String(20))

    # 初始化数据库连接:


engine = create_engine("mysql+pymysql://test:123456@127.0.0.1:3306/sct_study")

# 创建DBSession类型:
DBSession = sessionmaker(bind=engine)

'''
  1 连接数据库，并往表里面插入记录
'''

# # 创建一个sessioin对象
# session = DBSession()
#
# new_user = User(id='3',name='tomcat')
#
# # 添加到session
# session.add(new_user)
#
# # 提交即保存到数据库
# session.commit()
#
# # 关闭session
# session.close()


'''
2   连接数据库，查询记录并输出
'''

# 创建Session:
session = DBSession()
#
# # 创建Query查询，filter是where条件，最后调用one()返回唯一行，如果调用all()则返回所有行:
# user = session.query(User).filter(User.id == '1').first()
# print('type:', type(user))
# print('name:', user.name)

# 打印类型和对象的name属性:
# print(user.id,user.name)


'''把对象列表转换为字典列表'''


def convert_to_dicts(objs):
    obj_arr = []
    for o in objs:
        # 把Object对象转换成Dict对象
        dict = {}
        dict.update(o.__dict__)
        obj_arr.append(dict)

    return obj_arr


# 查询全部记录
userobjectlist = session.query(User).all()

user_dict_list = convert_to_dicts(userobjectlist)

print(type(user_dict_list))

for li in user_dict_list:
    for k, v in li.items():
        print(k)
        print(v)

# 关闭Session:
session.close()
